from mysqlphonenumber import MysqlHelp
import random
import openpyxl
myphone=MysqlHelp()
class Random_Phone(object):

    def obtain_data(self,provinces,city):
        phone_list=[]
        sql=f'select number_segment,provinces,city,card_type from phone_number where provinces="{provinces}" and city="{city}"'
        first_part=myphone.execute_sql(sql,mode='r')
        for item in first_part:
            phone_list.append(list(item))
        return phone_list


    def process_data(self,data,number):
        phone_list=[]
        last_part=[str(i)+str(j)+str(m)+str(n) for i in range(10) for j in range(10) for m in range(10) for n in range(10)]
        random.shuffle(data)
        for i in data[:number]:
            phone=i[0]+random.choice(last_part)
            i[0]=phone
            phone_list.append(i)
        if number<=len(data):
            phone_list=phone_list[:number]
        random.shuffle(phone_list)
        return phone_list


    def save_data(self,data):
        book=openpyxl.Workbook()
        n=1
        sheet1=book.create_sheet(str(n),n-1)
        sheet1.append(['号段', '省', '城市','卡类型'])
        for item in data:
            print(n,'n的值')
            print(sheet1.max_row)
            if sheet1.max_row==30000:
                sheet1=book.create_sheet(str(n+1),n)
                sheet1.append(['号段', '省', '城市','卡类型'])
                n+=1
            sheet1.append(item)

        book.save('./'+data[0][1]+data[0][2]+'.xlsx')
        

    def run(self,number,provinces,city):
    
        data=self.obtain_data(provinces,city)
        phone_list=self.process_data(data,number)
        self.save_data(phone_list)
       

if __name__ == "__main__":

    rp=Random_Phone()
    
    while True:
        number=input("请输入要随机生成的手机号的个数或输入q离开：")
        if number=='q':
            print('欢迎下次再来！！！！')
            break
        provinces=input("请输入要生成的手机号的归属地的省所在地：")
        city=input("请输入要生成的手机号的归属地城市名称：")
        if number.isdigit() and provinces.isalpha():
            rp.run(int(number),provinces,city)
            print('存储成功')
        else:
            print('数量或归属地输入有误，请重新输入！！！')
            

        
    
    